/*
 * DBManager.java
 *
 * Created on 2006년 3월 29일 (), ???? 9:31
 *
 * To change this template, choose Tools | Template Manager
 * and open the template in the editor.
 */

package com.zll.framework.db;


import java.sql.*;

import java.util.*;
import java.io.*;
import javax.naming.*;
import javax.sql.*;

import com.zll.framework.conf.Configuration;
import com.zll.framework.model.*;
import com.zll.framework.util.*;

/**
 *
 * @author eastar
 */
public class DBManager
{
	public static Connection getConnection() throws Exception {
		Context initContext = new InitialContext();
		Context envContext;
		if ("jeus".equals(Configuration.getWasName())) {
			envContext = (Context) initContext.lookup("");
		} else {	//tomcat
			envContext = (Context) initContext.lookup("java:/comp/env");
		}

		DataSource ds = (DataSource) envContext.lookup(Configuration.getDataSourceName());
		Connection conn = ds.getConnection();
		conn.setAutoCommit(false);
		//System.out.println("▷▷▷▷▷▷▷ getConnection() : " + conn);
		return conn;
	}

	public static void rollback(Connection conn) {
		if (conn != null)	try {
			conn.rollback();
			//System.out.println("▶▶▶▶▶▶▶ rollback() : " + conn);
		}
		catch (SQLException e) { }
	}

	public static void closeConnection(Connection conn) {
		//System.out.println("▶▶▶▶▶▶▶ closeConnection() : " + conn);
		if (conn != null) {
			try {
				conn.setAutoCommit(true);
				conn.close();
			} catch (SQLException e) {}
		}
	}

	public static void closeConnection(PreparedStatement pstmt) {
		if (pstmt != null) 	try {	pstmt.close();	} catch (SQLException e) {}
	}

	public static void closeConnection(ResultSet rs) {
		if (rs != null) 	try {	rs.close();	} catch (SQLException e) {}
	}

	public static void closeConnection(Connection conn, PreparedStatement pstmt, ResultSet rs)
	{
		if (rs != null)		try {	rs.close();		} catch (SQLException e) {}
		if (pstmt != null) 	try {	pstmt.close();	} catch (SQLException e) {}

		closeConnection(conn);
	}

	public static void closeConnection(Connection conn, Statement stmt, ResultSet rs)
	{
		if (rs != null)		try {	rs.close();		} catch (SQLException e) {}
		if (stmt != null) 	try {	stmt.close();	} catch (SQLException e) {}

		closeConnection(conn);
	}

	public static String getPageWhere(int currPage, int lineCount) throws Exception
	{
		String where 	= "";

		if (currPage > 0)
		{
			where = "\n LIMIT " + ((currPage - 1) * lineCount) + "," + lineCount;
		}

		return where;
	}

	public static boolean update(String xmlName, String sqlName, HashMap infos)
    {
		Connection conn = null;
        boolean result	= true;

        try
		{
			conn = DBManager.getConnection();

			DBManager.executeUpdate(xmlName, sqlName, infos, conn);

			conn.commit();
	    }
	    catch(Exception e)
	    {
	    	DBManager.rollback(conn);
	    	result 	= false;
	    	e.printStackTrace();
		}
	    finally
	    {
	    	DBManager.closeConnection(conn);
		}

        return result;
    }


	public static HashList executeQuery(String xmlName, String sqlName, HashMap infos, Connection conn)
	throws Exception
	{
		HashList arrList = new HashList();
		PreparedStatement   pstmt = null;
		ResultSet rs = null;
		String query = "";

		try
		{
			HashMap queryMap = SqlParser.getSql(xmlName, sqlName, infos);

			query = (String)queryMap.get("query");
			ArrayList bindList = (ArrayList)queryMap.get("bindList");

	      	printQuery(query, xmlName, sqlName, bindList);

	    	pstmt = conn.prepareStatement(query);
	    	setParameter(pstmt, bindList);
	      	rs = pstmt.executeQuery();

			ResultSetMetaData rsmd = rs.getMetaData();

			for(int i=1; i <= rsmd.getColumnCount(); i++)
			{
				arrList.setColumn(rsmd.getColumnLabel(i),	rsmd.getColumnType(i)
														, 	rsmd.getPrecision(i)
														, 	rsmd.getScale(i)
														,	rsmd.isNullable(i));
			}

			while (rs.next())
			{
				HashMap map = new HashMap();
				for(int i=1; i <= rsmd.getColumnCount(); i++)
				{
					map.put(rsmd.getColumnLabel(i), StringUtil.nvl(rs.getString(i)));
				}
				arrList.add(map);
			}

		}catch(Exception e){
			e.printStackTrace();
			throw e;
		}finally{
			closeConnection(pstmt);
			closeConnection(rs);
		}

		return arrList;
	}


	public static HashList executeQuery(String xmlName, String sqlName, String info, Connection conn)
	throws Exception
	{
		HashList arrList = new HashList();
		PreparedStatement   pstmt = null;
		ResultSet rs = null;
		String query = "";

		try
		{
			HashMap queryMap = SqlParser.getSql(xmlName, sqlName, info);

			query = (String)queryMap.get("query");
			ArrayList bindList = (ArrayList)queryMap.get("bindList");

	      	printQuery(query, xmlName, sqlName, bindList);

	    	pstmt = conn.prepareStatement(query);
	    	setParameter(pstmt, bindList);
	      	rs = pstmt.executeQuery();

			ResultSetMetaData rsmd = rs.getMetaData();

			for(int i=1; i <= rsmd.getColumnCount(); i++)
			{
				arrList.setColumn(rsmd.getColumnLabel(i),	rsmd.getColumnType(i)
														, 	rsmd.getPrecision(i)
														, 	rsmd.getScale(i)
														,	rsmd.isNullable(i));
			}


			while (rs.next())
			{
				HashMap map = new HashMap();
				for(int i=1; i <= rsmd.getColumnCount(); i++)
				{
					map.put(rsmd.getColumnLabel(i), StringUtil.nvl(rs.getString(i)));
				}
				arrList.add(map);
			}

		}catch(Exception e){
			e.printStackTrace();
			throw e;
		}finally{
			closeConnection(pstmt);
			closeConnection(rs);
		}

		return arrList;
	}


	public static HashList executeQuery(String xmlName, String sqlName, HashMap infos)
	throws Exception
	{
		HashList arrList = new HashList();
		Connection conn	= null;
		PreparedStatement   pstmt = null;
		ResultSet rs = null;
		String query = "";

		try
		{
			HashMap queryMap = SqlParser.getSql(xmlName, sqlName, infos);

			query = (String)queryMap.get("query");
			ArrayList bindList = (ArrayList)queryMap.get("bindList");


	      	printQuery(query, xmlName, sqlName, bindList);

			conn = getConnection();
	    	pstmt = conn.prepareStatement(query);
	    	setParameter(pstmt, bindList);
	      	rs = pstmt.executeQuery();

			ResultSetMetaData rsmd = rs.getMetaData();

			for(int i=1; i <= rsmd.getColumnCount(); i++)
			{
				arrList.setColumn(rsmd.getColumnLabel(i),	rsmd.getColumnType(i)
														, 	rsmd.getPrecision(i)
														, 	rsmd.getScale(i)
														,	rsmd.isNullable(i));
			}


			while (rs.next())
			{
				HashMap map = new HashMap();
				for(int i=1; i <= rsmd.getColumnCount(); i++)
				{
					map.put(rsmd.getColumnLabel(i), StringUtil.nvl(rs.getString(i)));
				}
				arrList.add(map);
			}

		}catch(Exception e){
			e.printStackTrace();
			throw e;
		}finally{
			closeConnection(conn, pstmt, rs);
		}

		return arrList;
	}


	public static HashList executeQuery(String xmlName, String sqlName, String info)
	throws Exception
	{
		HashList arrList = new HashList();
		Connection conn	= null;
		PreparedStatement   pstmt = null;
		ResultSet rs = null;
		String query = "";

		try
		{
			HashMap queryMap = SqlParser.getSql(xmlName, sqlName, info);

			query = (String)queryMap.get("query");
			ArrayList bindList = (ArrayList)queryMap.get("bindList");


	      	printQuery(query, xmlName, sqlName, bindList);

			conn = getConnection();
	    	pstmt = conn.prepareStatement(query);
	    	setParameter(pstmt, bindList);
	      	rs = pstmt.executeQuery();

			ResultSetMetaData rsmd = rs.getMetaData();

			for(int i=1; i <= rsmd.getColumnCount(); i++)
			{
				arrList.setColumn(rsmd.getColumnLabel(i),	rsmd.getColumnType(i)
														, 	rsmd.getPrecision(i)
														, 	rsmd.getScale(i)
														,	rsmd.isNullable(i));
			}


			while (rs.next())
			{
				HashMap map = new HashMap();
				for(int i=1; i <= rsmd.getColumnCount(); i++)
				{
					map.put(rsmd.getColumnLabel(i), StringUtil.nvl(rs.getString(i)));
				}
				arrList.add(map);
			}

		}catch(Exception e){
			e.printStackTrace();
			throw e;
		}finally{
			closeConnection(conn, pstmt, rs);
		}

		return arrList;
	}


	public static int executeUpdate(String xmlName, String sqlName, HashMap infos)
	throws Exception
	{
		Connection conn	= null;
		PreparedStatement   pstmt = null;
		String query = "";
		int result = 0;

		try
		{
			HashMap queryMap = SqlParser.getUpdateSql(xmlName, sqlName, infos);

			query = (String)queryMap.get("query");
			ArrayList bindList = (ArrayList)queryMap.get("bindList");


	      	printQuery(query, xmlName, sqlName, bindList);

			conn = getConnection();
	    	pstmt = conn.prepareStatement(query);
	    	setParameter(pstmt, bindList);
	      	result = pstmt.executeUpdate();

		}catch(Exception e){
			e.printStackTrace();
			throw e;
		}finally{
			closeConnection(conn);
			closeConnection(pstmt);
		}

		return result;
	}


	public static int executeUpdate(String xmlName, String sqlName, HashMap infos, Connection conn)
	throws Exception
	{
		PreparedStatement   pstmt = null;
		String query = "";
		int result = 0;

		try
		{
			HashMap queryMap = SqlParser.getUpdateSql(xmlName, sqlName, infos);

			query = (String)queryMap.get("query");
			ArrayList bindList = (ArrayList)queryMap.get("bindList");


	      	printQuery(query, xmlName, sqlName, bindList);

	    	pstmt = conn.prepareStatement(query);
	    	setParameter(pstmt, bindList);
	      	result = pstmt.executeUpdate();

		}catch(Exception e){
			e.printStackTrace();
			throw e;
		}finally{
			closeConnection(pstmt);
		}

		return result;
	}

	private static void setParameter(PreparedStatement pstmt, ArrayList bindList)
	throws Exception {
		for (int i = 0; i < bindList.size(); i++) {
    		Object obj	= bindList.get(i);
    		String className = (obj == null) ? "" : bindList.get(i).getClass().getName();

    		if (className.equals("java.lang.Integer")) {
    			pstmt.setInt(i+1, ((Integer)bindList.get(i)).intValue());
    		} else {
    			pstmt.setString(i+1, StringUtil.nvl(bindList.get(i)));
    		}
    	}
    }


    /*
	 * 디버거용 쿼리 출력
	 * @param	pstmt	PreparedStatement
	 * @param	list	ArrayList
	 */
	private static void printQuery(String query, String xmlName, String sqlName, ArrayList bindList) throws Exception
	{

		String sql = query;
		int sPos = sql.indexOf("?");
		int i = 0;

		while (sPos > -1) {
			Object obj	= bindList.get(i++);  //bindList의 type이 integer, String 등이 존재 하므로 integer, String Class의 parent인 Object로 받는다.
			                                  //그래서 아래  obj.getClass().getName() 을 하면 해당 값들의 class type의 이름이 return 된다.
    		String className = (obj == null) ? "" : obj.getClass().getName();
    		String bindValue = "";
    		if (className.equals("java.lang.Integer")) {
    			bindValue = StringUtil.nvl( String.valueOf((Integer)obj) );  //Object를 Integer형변화 후 다시 String.valueOf()로 String으로 변환
    			sql  = sql.substring(0, sPos) + ""+ bindValue +"" + sql.substring(sPos+1);
    		} else if (className.equals("java.lang.Double")) {
    			bindValue = StringUtil.nvl( String.valueOf((Double)obj) );  //Object를 Integer형변화 후 다시 String.valueOf()로 String으로 변환
    			sql  = sql.substring(0, sPos) + ""+ bindValue +"" + sql.substring(sPos+1);
    		}  else {
    			bindValue = StringUtil.nvl(obj).replaceAll("\\?", " ");
    			sql  = sql.substring(0, sPos) + "'"+ bindValue +"'" + sql.substring(sPos+1);
    		}
			sPos = sql.indexOf("?");
		}

		//if (!"/common.xml".equals(xmlName)) {
			System.out.println("\n\n------------------------------------------------------------------------------------------------" +
					"\n            <" + xmlName + "> : " + sqlName + " [" + StringUtil.getCurrentDate() +
					"\n------------------------------------------------------------------------------------------------" +
					"\n            " + sql.replaceAll("\t", "    ") +
					"\n------------------------------------------------------------------------------------------------\n");
		//}
    }
}